﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using Dapper;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using Autofac;
using System.Data.Common;
using System.Data.OracleClient;
using System.Data.OleDb;

namespace LiteExpress.Core.DbClient
{
    public static class DbFactory
    {
        #region members
        /// <summary>
        /// 存储Sql语句的词典（具体的查询语句级别）
        /// </summary>
        public static IDictionary<string, string> SqlMap = new Dictionary<string, string>();

        /// <summary>
        /// 默认的SQL文件目录 "SQLMap"
        /// </summary>
        public static string SQLMappingFilePath = "sqlmap";

        /// <summary>
        /// 默认的SQL文件格式 "*.sql.xml"
        /// </summary>
        public static string SQLMappingFilePattern = "*.sql.xml";
        #endregion

        #region method
        public static T GetClient<T>(string dbName, string sqlName, Func<IDbConnection, string, T> func)
        {
            var sql = ResolveSql(dbName, sqlName);            
            var conn = ResolveConnection(dbName);

            var result = func(conn, sql);
            return result;
        }

        public static T GetClient<T>(string dbName, Func<IDbConnection, T> func)
        {
            var conn = ResolveConnection(dbName);
            var result = func(conn);
            return result;
        }

        public static void GetClient(string dbName, string sqlName, Action<IDbConnection, string> func)
        {
            string sql = ResolveSql(dbName, sqlName);            
            var conn = ResolveConnection(dbName);
            func(conn, sql);
        }

        public static void GetClient(string dbName, Action<IDbConnection> func)
        {
            var conn = ResolveConnection(dbName);
            func(conn);
        }

        /// <summary>
        /// 获取数据库连接并创建事务，注意在使用dapper扩展方法的时候需要传入事务变量
        /// </summary>
        /// <example>
        /// DbFactory.GetTransactionConnection(DBName, (trans) =>
        /// {
        ///     var conn = trans.Connection;
        ///     string sql1 = "UPDATE Users SET NickName=@NickName WHERE UserID=@UserID";
        ///     string sql2 = "INSERT Users (UserID,UserName) VALUES (@UserID,@UserName)";
        ///     conn.Execute(sql1, new { UserID = 666, NickName = "tom" }, trans);//如果ado.net异常，外部会自动算作返回false，并执行rollback
        ///     conn.Execute(sql2, new { UserID = 333, UserName = "jerry"}, trans);
        ///     return true;
        /// });
        /// </example>
        public static bool GetTransaction(string dbName, Func<IDbTransaction, bool> func)
        {
            var conn = ResolveConnection(dbName);
            if (conn.State != System.Data.ConnectionState.Open)
            {
                conn.Open();
            }

            bool success = false;
            using (var trans = conn.BeginTransaction())
            {
                try
                {
                    success = func(trans);
                }
                catch (DataException)
                {
                    success = false;
                }

                if (success)
                {
                    trans.Commit();
                }
                else {
                    trans.Rollback();
                }
            }
            return success;
        }        
        #endregion

        #region get sql and connection
        /// <summary>
        /// 获取真实db连接，使用autofac容器
        /// </summary>
        private static IDbConnection ResolveConnection(string dbName)
        {
            return ContainerManager.Container.ResolveNamed<IDbConnection>("cloudconnection_" + dbName.ToLower());
        }        
        
        /// <summary>
        /// 获取Sql的定义
        /// </summary>
        public static string ResolveSql(string dbName, string sqlName)
        {
            var sqlKey = string.Format("{0}.{1}", dbName, sqlName).ToLower();
            if (SqlMap.ContainsKey(sqlKey) == false)
            {
                throw new ArgumentOutOfRangeException("sqlName", sqlName, "没有定义sqlName");
            }

            var oSql = SqlMap[sqlKey];
            return oSql;
        }
        #endregion

        #region initialize
        static DbFactory()
        {
            string sqlmappathConfig = ConfigurationManager.AppSettings["SQLMappingFilePath"];
            if (!string.IsNullOrWhiteSpace(sqlmappathConfig))
            {
                SQLMappingFilePath = sqlmappathConfig;
            }

            string sqlmapformatConfig = ConfigurationManager.AppSettings["SQLMappingFilePattern"];
            if (!string.IsNullOrWhiteSpace(sqlmapformatConfig))
            {
                SQLMappingFilePattern = sqlmapformatConfig;
            }

            LoadDatabases();
            LoadSqlModules();
        }
        
        /// <summary>
        /// 加载数据库节点(外部入口)
        /// </summary>
        private static void LoadDatabases()
        {
            #region 注册所有涉及到的数据库连接            
            ContainerManager.UpdateContainer(builder =>
            {
                foreach (ConnectionStringSettings dbConfig in ConfigurationManager.ConnectionStrings)
                {
                    DbProviderFactory fac = DbProviderFactories.GetFactory(dbConfig.ProviderName);
                    var conn = fac.CreateConnection();
                    Type connType = conn.GetType();

                    builder.RegisterType(connType)
                        .WithParameter("connectionString", dbConfig.ConnectionString)//输入构造函数参数
                        //.InstancePerLifetimeScope()//每次请求一个连接实例，由于dapper默认使用datareader，所以此种模式下会导致并发下reader未关闭占用连接的情况。
                        //.ExternallyOwned()
                        .Named<IDbConnection>("cloudconnection_" + dbConfig.Name.ToLower());//命名数据库连接实例
                }            
            });            
            #endregion
        }

        /// <summary>
        /// 加载SqlMap到内存中
        /// </summary>
        private static void LoadSqlModules()
        {
            var basePath = AppDomain.CurrentDomain.BaseDirectory;
            string sqlmapDir = string.Format("{0}{1}", basePath, SQLMappingFilePath);
            if (!Directory.Exists(sqlmapDir))
            {
                return;
            }

            string fileformat = Path.GetExtension(SQLMappingFilePattern);

            var sqlFiles = Directory.GetFiles(sqlmapDir, SQLMappingFilePattern, SearchOption.AllDirectories);
            foreach (var sqlFile in sqlFiles)
            {
                if (fileformat.Equals(".xml"))
                {
                    #region xml
                    var sqlMapXml = new XmlDocument();
                    sqlMapXml.Load(sqlFile);

                    var nsmgr = new XmlNamespaceManager(sqlMapXml.NameTable);
                    nsmgr.AddNamespace("sm", "http://LiteExpress.Serivice/SQLMapping/");

                    var moduleNodes = sqlMapXml.DocumentElement.SelectNodes("sm:Module", nsmgr);
                    foreach (XmlNode moduleNode in moduleNodes)
                    {
                        #region Module
                        var moduleName = moduleNode.Attributes["moduleName"].InnerText;
                        var dbName = moduleNode.Attributes["dbName"].InnerText;

                        var sqlMapElementes = moduleNode.SelectNodes("sm:ObjectSql", nsmgr);

                        foreach (XmlNode element in sqlMapElementes)
                        {
                            string sql = element.InnerText;
                            string sqlName = string.Format("{0}.{1}.{2}"
                                , dbName, moduleName, element.Attributes["sqlName"].InnerText);
                            SqlMap[sqlName.ToLower()] = sql;
                        }
                        #endregion
                    }
                    #endregion
                }

                if (fileformat.Equals(".json"))
                {
                    #region json
                    using (var stream = new StreamReader(sqlFile))
                    {
                        string json = stream.ReadToEnd();
                        stream.Close();
                        var sqlmap = Newtonsoft.Json.JsonConvert.DeserializeObject<JsonSqlMap>(json);
                        sqlmap.dbs.SelectMany(
                            m => m.sqls.Select(
                                obj => new KeyValuePair<string, string>(string.Format("{0}.{1}.{2}", m.dbName, sqlmap.module, obj.name), obj.sql)
                            )
                        ).ToList()
                        .ForEach(kv=>{
                            SqlMap[kv.Key.ToLower()] = kv.Value;
                        });
                    }
                    #endregion
                }
            }
        }
        #region json sqlmap
        [Serializable]
        private class JsonSqlMap
        {
            public string module { get; set; }
            public List<JsonSqlmapDb> dbs { get; set; }
        }

        [Serializable]
        private class JsonSqlmapDb
        {
            public string dbName { get; set; }

            public List<JsonSqlObj> sqls { get; set; }
        }

        [Serializable]
        private class JsonSqlObj
        {
            public string name { get; set; }
            public string sql { get; set; }
        }
        #endregion
        #endregion        
    }
}
